SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[SP_TMDOCU_CLIE_Q05]
/*-------------------------------------------------------*/
/*---Empresa               OFISIS S.A.                --*/
/*---Cliente               SANTILLANA                 --*/
/*---Sistema               Tesoreria                  --*/
/*---M¸dulo                Empresa                    --*/
/*---Programa              HISTORIA DE DOCUMENTO       --*/
/*---Script                SP_TMDOCU_CLIE_Q05.sql     --*/
/*---Nombre SP             SP_TMDOCU_CLIE_Q05         --*/
/*---Desarrollado por      Reynaldo Menacho Luyo      --*/
/*---Fecha Creaci¸n        08/08/2001                 --*/
/*---Base Datos            SQL SERVER		      --*/
/*---Versi¸n               2000                       --*/
/*---Invoca a SP                                      --*/
/*-------------------------------------------------------*/

/*-------------------------------------------------------*/
/*---Modificado 1 por     					 --*/
/*---Fecha Modificaci¸n   					 --*/ 
/*---Detalle Modificaci¸n 					 --*/
/*-------------------------------------------------------*/

/*-------------------------------------------------------*/
/*---Drop Proc SP_TMDOCU_CLIE_Q05                      --*/
/*---Grant all on SP_TMDOCU_CLIE_Q05 to public         --*/ 
/*---SP_TMDOCU_CLIE_Q05 '01','LET','001180-0','2001/09/28','C'   --*/
/*-------------------------------------------------------*/

@ISCO_EMPR 	TD_VC_002,
@ISTI_DOCU 	TD_VC_003,
@ISNU_DOCU 	TD_VC_020,
@IDFE_CANC	TD_DT_001,
@ISTI_AUXI_CLIE	TD_VC_001
	As
CREATE TABLE #TWDOCU_CLIE_Q05_1 
 ( CO_EMPR      varchar(3) NULL,
   CO_TIPO_DOCU	varchar(3)  NULL ,
   NU_DOCU_CLIE	varchar(20)  NULL,
   CO_CLIE	varchar(20) NULL ,
   FE_REPO	datetime NULL,
   CO_MONE	varchar(03) NULL,
   FA_TIPO_CAMB	numeric(15,6) NULL,
   IM_CXCB_NACI	numeric(16,4) NULL,
   IM_CXCB_EXTR	numeric(16,4) NULL,
   CO_CAJA	varchar(8) NULL,
   NU_AMAR	varchar(8) NULL,
   NU_CANJ	varchar(8) NULL,
   NU_RENO	varchar(8) NULL,
   CO_BANC	varchar(3) NULL,
   NU_CNTA_BANC	varchar(100) NULL,    
   NU_COMP_CABA	varchar(20) NULL, 
   IM_PAGO_NACI	numeric(16,4) NULL,
   IM_PAGO_EXTR	numeric(16,4) NULL,
   TI_DETA	varchar(1) NULL,
   ST_FLAG	varchar(1) NULL,
   ST_RENO	varchar(1) NULL)

CREATE TABLE #TWDOCU_CLIE_Q05_2 
 ( CO_EMPR      varchar(3) NULL,
   CO_CLIE	varchar(20)  NULL ,
   NU_RENO	varchar(20)  NULL)

DECLARE 

@VSDE_LETR  	TD_VC_100,
@VSCO_EMPR		TD_VC_002,
@VSCO_CLIE  	TD_VC_020,
@VSNU_CANJ  	TD_VC_008,
@VSNU_RENO  	TD_VC_008,
@VSNU_LETR	TD_VC_020,
@VNNU_LONG	NUMERIC(3,0),
--@VSDE_CODI 	VARCHAR(500)

@CSCO_TIPO_DOCU 	TD_VC_003,
@CSCO_EMPR  	TD_VC_002,
@CSCO_CLIE  	TD_VC_020,
@CSNU_CANJ  	TD_VC_008,
@CSNU_RENO  	TD_VC_008,
@CSNU_LETR_CLIE	TD_VC_008,

@OSTI_DOCU 		TD_VC_003,
@OSTI_DOCU_0001	TD_VC_003,
@OSTI_DOCU_0002	TD_VC_003


SELECT @VNNU_LONG = 1
-- TIPOS DE DETALLE(TI_DETA = '1' AL '7')
--1.- DOCS EMITIDOS EN ESOS MESES					
--2.- PAGOS BANCOS / CAJA
--3.- APLICACIONES DE NCR A DOCUMENTOS
--4.- CANJES
--5.- RENOVACIONES
--6.- APLICACION DE DOC. A ANTICIPOS
--8.- ANTICIPOS( SIN APLICACIONES, SALDO = IMPORTE DEPOSITO )
--7.- ANTICIPOS( CON APLICACION, SALDOS)


--1.- DOCS EMITIDOS EN ESOS MESES
-- TMDOCU_CLIE

INSERT INTO #TWDOCU_CLIE_Q05_1 
(CO_EMPR, CO_TIPO_DOCU, NU_DOCU_CLIE, CO_CLIE, FE_REPO, 
CO_MONE, FA_TIPO_CAMB, IM_CXCB_NACI, IM_CXCB_EXTR, TI_DETA)
Select T1.CO_EMPR, T1.CO_TIPO_DOCU , T1.NU_DOCU_CLIE , T1.CO_CLIE, T1.FE_EMIS, 
T1.CO_MONE, T1.FA_TIPO_CAMB,
PatIndex( '0', LTRIM(RTRIM(STR(PatIndex(T1.CO_MONE, 'DOL')))))*( PatIndex(T2.ST_SIGN,'S') - PatIndex(T2.ST_SIGN,'N') )*
T1.IM_TOTA,
PatIndex(T1.CO_MONE, 'DOL')*( PatIndex(T2.ST_SIGN,'S') - PatIndex(T2.ST_SIGN,'N') )*
T1.IM_TOTA,
'1'
From  TMDOCU_CLIE T1, TTDOCU_CNTB T2
Where T1.CO_EMPR = @ISCO_EMPR
AND T1.CO_TIPO_DOCU = @ISTI_DOCU
AND T1.NU_DOCU_CLIE = @ISNU_DOCU
And T1.CO_ESTA_DOCU != 'ANU'
AND T1.CO_TIPO_DOCU = T2.TI_DOCU


-- TMLETR_CLIE

IF @ISTI_DOCU = 'LET'
BEGIN
   INSERT INTO #TWDOCU_CLIE_Q05_1 
   (CO_EMPR, CO_TIPO_DOCU, NU_DOCU_CLIE, CO_CLIE, FE_REPO, 
   CO_MONE, FA_TIPO_CAMB, IM_CXCB_NACI, IM_CXCB_EXTR, TI_DETA)
   Select T1.CO_EMPR, 'LET', T1.NU_LETR_CLIE, T1.CO_CLIE, T1.FE_EMIS, 
   T1.CO_MONE, T1.FA_TIPO_CAMB,
   PatIndex( '0', LTRIM(RTRIM(STR(PatIndex(T1.CO_MONE, 'DOL')))))*T1.IM_TOTA,
   PatIndex(T1.CO_MONE, 'DOL')*T1.IM_TOTA, '1'
   From   TMLETR_CLIE T1
   Where T1.CO_EMPR = @ISCO_EMPR
   --And T1.FE_EMIS <= @IDFE_EMIS_FINA
   And T1.CO_ESTA_DOCU != 'ANU'
   AND T1.NU_LETR_CLIE = @ISNU_DOCU
   
      --BUSCA LETRAS RENOVADAS
/*
   WHILE @VNNU_LONG > 0
   BEGIN
	INSERT INTO #TWDOCU_CLIE_Q05_2 (CO_EMPR, CO_CLIE, NU_RENO)
	SELECT 	T1.CO_EMPR, T1.CO_CLIE, T1.NU_RENO
	FROM 	TDRENO_CLIE T1, #TWDOCU_CLIE_Q05_1 T2
	WHERE 	T1.CO_EMPR = @ISCO_EMPR
	AND 	T1.CO_TIPO_DOCU IS NOT NULL 
	AND 	T1.NU_DOCU_CLIE  IS NOT NULL
	AND 	T2.CO_EMPR = T1.CO_EMPR
	AND	T2.ST_FLAG IS NULL
	AND	T2.CO_TIPO_DOCU = T1.CO_TIPO_DOCU 
	AND 	T2.NU_DOCU_CLIE = T1.NU_DOCU_CLIE

	UPDATE 	#TWDOCU_CLIE_Q05_1
	SET ST_FLAG = '*'
	
	SELECT 	@VNNU_LONG = COUNT(*) FROM #TWDOCU_CLIE_Q05_2

	INSERT INTO #TWDOCU_CLIE_Q05_1 
	(CO_EMPR, CO_TIPO_DOCU, NU_DOCU_CLIE, CO_CLIE, FE_REPO,
	CO_MONE, FA_TIPO_CAMB, IM_CXCB_NACI, IM_CXCB_EXTR, TI_DETA, ST_RENO)
	Select T1.CO_EMPR, 'LET', T1.NU_LETR_CLIE, T1.CO_CLIE, T1.FE_EMIS,
	T1.CO_MONE, T1.FA_TIPO_CAMB,
	PatIndex( '0', LTRIM(RTRIM(STR(PatIndex(T1.CO_MONE, 'DOL')))))*T1.IM_TOTA,
	PatIndex(T1.CO_MONE, 'DOL')*T1.IM_TOTA, '1', 'S'
	From   TMLETR_CLIE T1, TDRENO_CLIE T2, #TWDOCU_CLIE_Q05_2 T3
	Where T1.CO_EMPR = @ISCO_EMPR
	--And T1.FE_EMIS <= @IDFE_EMIS_FINA
	And T1.CO_ESTA_DOCU != 'ANU'
	AND T2.CO_EMPR = T1.CO_EMPR
	AND T2.CO_CLIE = T1.CO_CLIE
	--AND T2.NU_RENO = T1.NU_RENO
	AND T2.NU_LETR_CLIE = T1.NU_LETR_CLIE
	AND T2.CO_EMPR = T3.CO_EMPR
	AND T2.CO_CLIE = T3.CO_CLIE
	AND T2.NU_RENO = T3.NU_RENO
	AND NOT EXISTS( SELECT T4.NU_DOCU_CLIE FROM #TWDOCU_CLIE_Q05_1 T4
			WHERE T4.CO_EMPR = T2.CO_EMPR
			AND T4.CO_CLIE = T2.CO_CLIE
			AND T4.CO_TIPO_DOCU = 'LET'
			AND T4.NU_DOCU_CLIE = T2.NU_LETR_CLIE)
	DELETE #TWDOCU_CLIE_Q05_2

   END
		
	UPDATE 	#TWDOCU_CLIE_Q05_1
	SET TI_DETA = '5', IM_PAGO_NACI = T1.IM_CXCB_NACI, IM_CXCB_NACI = 0,
	IM_PAGO_EXTR = T1.IM_CXCB_EXTR, IM_CXCB_EXTR = 0, NU_RENO = T2.NU_RENO
	FROM #TWDOCU_CLIE_Q05_1 T1, TDRENO_CLIE T2
	WHERE T1.CO_EMPR = T2.CO_EMPR
	AND T1.CO_CLIE = T2.CO_CLIE
	AND T1.CO_TIPO_DOCU = T2.CO_TIPO_DOCU
	AND T1.NU_DOCU_CLIE = T2.NU_DOCU_CLIE 	

	INSERT INTO #TWDOCU_CLIE_Q05_1 
	(CO_EMPR, CO_TIPO_DOCU, NU_DOCU_CLIE, CO_CLIE, FE_REPO, 
	CO_MONE, FA_TIPO_CAMB, IM_CXCB_NACI, IM_CXCB_EXTR, TI_DETA)
	Select CO_EMPR, CO_TIPO_DOCU, NU_DOCU_CLIE, CO_CLIE, FE_REPO, 
	CO_MONE, FA_TIPO_CAMB, IM_PAGO_NACI, IM_PAGO_EXTR, '1'
	From  #TWDOCU_CLIE_Q05_1
	Where TI_DETA = '5'
*/
END
	
--2.- PAGOS BANCOS / CAJA
INSERT INTO #TWDOCU_CLIE_Q05_1 
(CO_EMPR, CO_TIPO_DOCU, NU_DOCU_CLIE, CO_CLIE, FE_REPO, 
CO_MONE, FA_TIPO_CAMB,
CO_BANC, NU_CNTA_BANC, NU_COMP_CABA,
IM_PAGO_NACI, IM_PAGO_EXTR, TI_DETA)
Select T1.CO_EMPR, T1.TI_DOCU_ENTI, T1.NU_DOCU_ENTI, T1.CO_ENTI, T2.FE_OPER,
T4.CO_MONE, T2.FA_TIPO_CAMB,
T1.CO_BANC, T1.NU_CNTA_BANC, T1.NU_COMP_BANC,

( ( PatIndex(T5.ST_SIGN,'S') - PatIndex(T5.ST_SIGN,'N') )*PatIndex(T1.TI_MOVI_BANC,'I')-
  ( PatIndex(T5.ST_SIGN,'S') - PatIndex(T5.ST_SIGN,'N') )*PatIndex(T1.TI_MOVI_BANC,'E')
)*(
PatIndex(T1.ST_CIER_DOCU, 'N')*
PatIndex('0', LTRIM(RTRIM(STR(PatIndex(T4.CO_MONE, 'DOL')))))*T1.IM_CONV_DOCU +
PatIndex(T1.ST_CIER_DOCU, 'S')*
PatIndex('0', LTRIM(RTRIM(STR(PatIndex(T4.CO_MONE, 'DOL')))))*T1.IM_SALD_DOCU ),


( ( PatIndex(T5.ST_SIGN,'S') - PatIndex(T5.ST_SIGN,'N') )*PatIndex(T1.TI_MOVI_BANC,'I')-
  ( PatIndex(T5.ST_SIGN,'S') - PatIndex(T5.ST_SIGN,'N') )*PatIndex(T1.TI_MOVI_BANC,'E')
)*(
PatIndex(T1.ST_CIER_DOCU, 'N')*
PatIndex(T4.CO_MONE, 'DOL')*T1.IM_CONV_DOCU+
PatIndex(T1.ST_CIER_DOCU, 'S')*

PatIndex(T4.CO_MONE, 'DOL')*T1.IM_SALD_DOCU), 

'2'
From  TDMOVI_BANC T1, TCMOVI_BANC T2, TMCNTA_BANC T3, TMDOCU_CLIE T4, TTDOCU_CNTB T5
Where T4.CO_EMPR = @ISCO_EMPR
AND   T4.CO_TIPO_DOCU = @ISTI_DOCU 
AND   T4.NU_DOCU_CLIE = @ISNU_DOCU
--And    T1.TI_MOVI_BANC = 'I' 
And    T1.TI_ENTI = 'C'
And    T2.FE_OPER  <= @IDFE_CANC
And    T2.TI_SITU != 'ANU' 

And    T1.CO_EMPR = T2.CO_EMPR  
And    T1.CO_UNID_CONC = T2.CO_UNID_CONC
And    T1.CO_BANC = T2.CO_BANC  
And    T1.NU_CNTA_BANC  = T2.NU_CNTA_BANC  
And    T1.AA_BNCO = T2.AA_BNCO
And    T1.MM_BNCO = T2.MM_BNCO 
And    T1.TI_MOVI_BANC  = T2.TI_MOVI_BANC 
And    T1.NU_COMP_BANC = T2.NU_COMP_BANC

And    T1.CO_EMPR = T3.CO_EMPR  
And    T1.CO_BANC = T3.CO_BANC  
And    T1.NU_CNTA_BANC  = T3.NU_CNTA_BANC

AND	 T1.CO_EMPR = T4.CO_EMPR
AND 	 T1.TI_DOCU_ENTI = T4.CO_TIPO_DOCU
AND 	 T1.NU_DOCU_ENTI = T4.NU_DOCU_CLIE
	
AND 	 T4.CO_TIPO_DOCU = T5.TI_DOCU
 
INSERT INTO #TWDOCU_CLIE_Q05_1 
(CO_EMPR, CO_TIPO_DOCU, NU_DOCU_CLIE, CO_CLIE, FE_REPO, 

CO_MONE, FA_TIPO_CAMB,
CO_CAJA, NU_COMP_CABA,
IM_PAGO_NACI, IM_PAGO_EXTR, TI_DETA)
Select T1.CO_EMPR, T1.TI_DOCU_ENTI, T1.NU_DOCU_ENTI, T1.CO_ENTI, T2.FE_OPER,
T4.CO_MONE, T2.FA_TIPO_CAMB,
T1.CO_CAJA, T1.NU_COMP_CAJA, 

( ( PatIndex(T5.ST_SIGN,'S') - PatIndex(T5.ST_SIGN,'N') )*PatIndex(T1.TI_MOVI,'I')-
  ( PatIndex(T5.ST_SIGN,'S') - PatIndex(T5.ST_SIGN,'N') )*PatIndex(T1.TI_MOVI,'E')
)*(
PatIndex(T1.ST_CIER_DOCU, 'N')*
PatIndex('0', LTRIM(RTRIM(STR(PatIndex(T4.CO_MONE, 'DOL')))))*T1.IM_CONV_DOCU +
PatIndex(T1.ST_CIER_DOCU, 'S')*
PatIndex('0', LTRIM(RTRIM(STR(PatIndex(T4.CO_MONE, 'DOL')))))*T1.IM_SALD_DOCU),

( ( PatIndex(T5.ST_SIGN,'S') - PatIndex(T5.ST_SIGN,'N') )*PatIndex(T1.TI_MOVI,'I')-
  ( PatIndex(T5.ST_SIGN,'S') - PatIndex(T5.ST_SIGN,'N') )*PatIndex(T1.TI_MOVI,'E')
)*(
PatIndex(T1.ST_CIER_DOCU, 'N')*
PatIndex(T4.CO_MONE, 'DOL')*T1.IM_CONV_DOCU+
PatIndex(T1.ST_CIER_DOCU, 'S')*
PatIndex(T4.CO_MONE, 'DOL')*T1.IM_SALD_DOCU), 

'2'
From   TDMOVI_CAJA T1, TCMOVI_CAJA T2, TMCAJA T3, TMDOCU_CLIE T4, TTDOCU_CNTB T5
Where  T4.CO_EMPR = @ISCO_EMPR
AND 	T4.CO_TIPO_DOCU = @ISTI_DOCU
AND 	T4.NU_DOCU_CLIE = @ISNU_DOCU
--And    T1.TI_MOVI = 'I'
And    T1.TI_ENTI = @ISTI_AUXI_CLIE
And    T2.FE_OPER  <= @IDFE_CANC
And    T2.TI_SITU != 'ANU' 

And    T1.CO_EMPR = T2.CO_EMPR  
And    T1.CO_CAJA = T2.CO_CAJA
And    T1.AA_CAJA = T2.AA_CAJA
And    T1.MM_CAJA = T2.MM_CAJA 
And    T1.TI_MOVI = T2.TI_MOVI
And    T1.NU_COMP_CAJA = T2.NU_COMP_CAJA

And    T1.CO_EMPR = T3.CO_EMPR  
And    T1.CO_CAJA = T3.CO_CAJA

And	 T1.CO_EMPR = T4.CO_EMPR
AND	 T1.TI_DOCU_ENTI = T4.CO_TIPO_DOCU
And	 T1.NU_DOCU_ENTI = T4.NU_DOCU_CLIE

AND 	 T4.CO_TIPO_DOCU = T5.TI_DOCU



-- PAGOS LETRAS 
--If PatIndex('%LET%', @ISTI_DOCU) > 0 
	
INSERT INTO #TWDOCU_CLIE_Q05_1 
(CO_EMPR, CO_TIPO_DOCU, NU_DOCU_CLIE, CO_CLIE, FE_REPO, 
CO_MONE, FA_TIPO_CAMB,
CO_BANC, NU_CNTA_BANC, NU_COMP_CABA,
IM_PAGO_NACI, IM_PAGO_EXTR, TI_DETA)
Select T1.CO_EMPR, T1.TI_DOCU_ENTI, T1.NU_DOCU_ENTI, T1.CO_ENTI, T2.FE_OPER,
T4.CO_MONE, T2.FA_TIPO_CAMB,
T1.CO_BANC, T1.NU_CNTA_BANC, T1.NU_COMP_BANC,
 
(PatIndex(T1.TI_MOVI_BANC,'I') - PatIndex(T1.TI_MOVI_BANC,'E'))*(
PatIndex(T1.ST_CIER_DOCU, 'N')*
PatIndex('0', LTRIM(RTRIM(STR(PatIndex(T4.CO_MONE, 'DOL')))))*T1.IM_CONV_DOCU +
PatIndex(T1.ST_CIER_DOCU, 'S')*
PatIndex('0', LTRIM(RTRIM(STR(PatIndex(T4.CO_MONE, 'DOL')))))*T1.IM_SALD_DOCU),

(PatIndex(T1.TI_MOVI_BANC,'I') - PatIndex(T1.TI_MOVI_BANC,'E'))*(
PatIndex(T1.ST_CIER_DOCU, 'N')*
PatIndex(T4.CO_MONE, 'DOL')*T1.IM_CONV_DOCU+
PatIndex(T1.ST_CIER_DOCU, 'S')*
PatIndex(T4.CO_MONE, 'DOL')*T1.IM_SALD_DOCU), 
'2'
From  TDMOVI_BANC T1, TCMOVI_BANC T2, TMCNTA_BANC T3, TMLETR_CLIE T4, #TWDOCU_CLIE_Q05_1 T5
Where  T4.CO_EMPR = @ISCO_EMPR
And    T1.TI_ENTI = @ISTI_AUXI_CLIE
And    T2.FE_OPER  <= @IDFE_CANC
And    T2.TI_SITU != 'ANU' 

And    T1.CO_EMPR = T2.CO_EMPR  
And    T1.CO_UNID_CONC = T2.CO_UNID_CONC
And    T1.CO_BANC = T2.CO_BANC  
And    T1.NU_CNTA_BANC  = T2.NU_CNTA_BANC  
And    T1.AA_BNCO = T2.AA_BNCO
And    T1.MM_BNCO = T2.MM_BNCO 
And    T1.TI_MOVI_BANC  = T2.TI_MOVI_BANC 
And    T1.NU_COMP_BANC = T2.NU_COMP_BANC

And    T1.CO_EMPR = T3.CO_EMPR  
And    T1.CO_BANC = T3.CO_BANC  
And    T1.NU_CNTA_BANC  = T3.NU_CNTA_BANC

AND	 T1.CO_EMPR = T4.CO_EMPR
AND	 T1.TI_DOCU_ENTI = 'LET'
AND 	 T1.NU_DOCU_ENTI = T4.NU_LETR_CLIE

AND	 T1.CO_EMPR = T5.CO_EMPR
AND	 T1.TI_DOCU_ENTI = T5.CO_TIPO_DOCU
AND 	 T1.NU_DOCU_ENTI = T5.NU_DOCU_CLIE
--AND	 T5.ST_RENO = 'S'

	
INSERT INTO #TWDOCU_CLIE_Q05_1 
(CO_EMPR, CO_TIPO_DOCU, NU_DOCU_CLIE, CO_CLIE, FE_REPO, 
CO_MONE, FA_TIPO_CAMB,
CO_CAJA, NU_COMP_CABA, 
IM_PAGO_NACI, IM_PAGO_EXTR, TI_DETA)
Select T1.CO_EMPR, T1.TI_DOCU_ENTI, T1.NU_DOCU_ENTI, T1.CO_ENTI, T2.FE_OPER,
T4.CO_MONE, T2.FA_TIPO_CAMB,
T1.CO_CAJA, T1.NU_COMP_CAJA,

(PatIndex(T1.TI_MOVI,'I') - PatIndex(T1.TI_MOVI,'E'))*(
PatIndex(T1.ST_CIER_DOCU, 'N')*
PatIndex('0', LTRIM(RTRIM(STR(PatIndex(T4.CO_MONE, 'DOL')))))*T1.IM_CONV_DOCU +
PatIndex(T1.ST_CIER_DOCU, 'S')*
PatIndex('0', LTRIM(RTRIM(STR(PatIndex(T4.CO_MONE, 'DOL')))))*T1.IM_SALD_DOCU),

(PatIndex(T1.TI_MOVI,'I') - PatIndex(T1.TI_MOVI,'E'))*(
PatIndex(T1.ST_CIER_DOCU, 'N')*
PatIndex(T4.CO_MONE, 'DOL')*T1.IM_CONV_DOCU+
PatIndex(T1.ST_CIER_DOCU, 'S')*
PatIndex(T4.CO_MONE, 'DOL')*T1.IM_SALD_DOCU), 
'2'
From   TDMOVI_CAJA T1, TCMOVI_CAJA T2, TMCAJA T3, TMLETR_CLIE T4, #TWDOCU_CLIE_Q05_1 T5
Where  T4.CO_EMPR = @ISCO_EMPR
And    T1.TI_ENTI = @ISTI_AUXI_CLIE
And    T2.FE_OPER  <= @IDFE_CANC
And    T2.TI_SITU != 'ANU' 
And    T1.CO_EMPR = T2.CO_EMPR  
And    T1.CO_CAJA = T2.CO_CAJA
And    T1.AA_CAJA = T2.AA_CAJA
And    T1.MM_CAJA = T2.MM_CAJA 
And    T1.TI_MOVI = T2.TI_MOVI
And    T1.NU_COMP_CAJA = T2.NU_COMP_CAJA

And    T1.CO_EMPR = T3.CO_EMPR  
And    T1.CO_CAJA = T3.CO_CAJA

And	 T1.CO_EMPR = T4.CO_EMPR
AND	 T1.TI_DOCU_ENTI = 'LET'
And	 T1.NU_DOCU_ENTI = T4.NU_LETR_CLIE

AND	 T1.CO_EMPR = T5.CO_EMPR
AND	 T1.TI_DOCU_ENTI = T5.CO_TIPO_DOCU
AND 	 T1.NU_DOCU_ENTI = T5.NU_DOCU_CLIE
--AND	 T5.ST_RENO = 'S'
	
--3.- APLICACIONES

-- DOCUMENTOS
INSERT INTO #TWDOCU_CLIE_Q05_1 
(CO_EMPR, CO_TIPO_DOCU, NU_DOCU_CLIE, CO_CLIE, FE_REPO, 
CO_MONE, FA_TIPO_CAMB,
NU_AMAR,
IM_PAGO_NACI, IM_PAGO_EXTR, TI_DETA)
SELECT T1.CO_EMPR, T4.CO_TIPO_DOCU, T4.NU_DOCU_CLIE, T1.CO_CLIE, T1.FE_AMAR,
T4.CO_MONE, T1.FA_TIPO_CAMB,
T1.NU_AMAR, 
PatIndex( '0', LTRIM(RTRIM(STR(PatIndex(T4.CO_MONE, 'DOL')))))*(
   PatIndex(T1.CO_MONE, T4.CO_MONE) +
   PatIndex( '0', LTRIM(RTRIM(STR(PatIndex(T1.CO_MONE, T4.CO_MONE)))))*T1.FA_TIPO_CAMB)*
(PatIndex(T5.ST_SIGN,'S') - PatIndex(T5.ST_SIGN,'N') )*
T2.IM_COBR_DEST,
PatIndex( T4.CO_MONE, 'DOL')*(
   PatIndex(T1.CO_MONE, T4.CO_MONE) +
   PatIndex( '0', LTRIM(RTRIM(STR(PatIndex(T1.CO_MONE, T4.CO_MONE)))))/T1.FA_TIPO_CAMB)*
(PatIndex(T5.ST_SIGN,'S') - PatIndex(T5.ST_SIGN,'N') )*
T2.IM_COBR_DEST,
'3'
From   TCAMAR_NCCL T1 , TDAMAR_NCCL T2, TMDOCU_CLIE T4, TTDOCU_CNTB T5
Where T4.CO_EMPR = @ISCO_EMPR
AND 	T4.CO_TIPO_DOCU = @ISTI_DOCU
AND 	T4.NU_DOCU_CLIE = @ISNU_DOCU
AND	T1.CO_EMPR =   T4.CO_EMPR
AND	T4.CO_TIPO_DOCU != 'N/C'
And    T1.FE_AMAR  <= @IDFE_CANC
AND    T1.TI_SITU != 'ANU'
AND    T2.CO_TIDO_DEST = T4.CO_TIPO_DOCU 
AND    T2.NU_DOCU_DEST = T4.NU_DOCU_CLIE 
AND    T1.CO_EMPR = T2.CO_EMPR  
AND    T1.NU_AMAR = T2.NU_AMAR
AND    T2.CO_EMPR = T4.CO_EMPR
AND    T1.CO_CLIE = T4.CO_CLIE
AND    T2.CO_TIDO_DEST = T4.CO_TIPO_DOCU
AND    T2.NU_DOCU_DEST = T4.NU_DOCU_CLIE
AND	 T4.CO_ESTA_DOCU != 'ANU' 
And    T4.NU_REND_GAST IS NULL
AND	 T4.CO_TIPO_DOCU = T5.TI_DOCU

-- NOTAS DE CREDITO APLICADAS 
INSERT INTO #TWDOCU_CLIE_Q05_1 
(CO_EMPR, CO_TIPO_DOCU, NU_DOCU_CLIE, CO_CLIE, FE_REPO, 
CO_MONE, FA_TIPO_CAMB,

NU_AMAR,
IM_PAGO_NACI, IM_PAGO_EXTR, TI_DETA)
SELECT T1.CO_EMPR, T4.CO_TIPO_DOCU, T4.NU_DOCU_CLIE, T1.CO_CLIE, T1.FE_AMAR,
T4.CO_MONE, T1.FA_TIPO_CAMB,
T1.NU_AMAR,
PatIndex( '0', LTRIM(RTRIM(STR(PatIndex(T4.CO_MONE, 'DOL')))))*(
   PatIndex(T1.CO_MONE, T4.CO_MONE) +
   PatIndex( '0', LTRIM(RTRIM(STR(PatIndex(T1.CO_MONE, T4.CO_MONE)))))*T1.FA_TIPO_CAMB)*
(PatIndex(T5.ST_SIGN,'S') - PatIndex(T5.ST_SIGN,'N') )*
T2.IM_COBR_ORIG,
PatIndex(T4.CO_MONE, 'DOL')*(
   PatIndex(T1.CO_MONE, T4.CO_MONE) +
   PatIndex( '0', LTRIM(RTRIM(STR(PatIndex(T1.CO_MONE, T4.CO_MONE)))))/T1.FA_TIPO_CAMB)*
(PatIndex(T5.ST_SIGN,'S') - PatIndex(T5.ST_SIGN,'N') )*
T2.IM_COBR_ORIG,
'3'
From   TCAMAR_NCCL T1 , TDAMAR_NCCL T2, TMDOCU_CLIE T4, TTDOCU_CNTB T5
Where  T4.CO_EMPR = @ISCO_EMPR
AND 	T4.CO_TIPO_DOCU = @ISTI_DOCU
AND 	T4.NU_DOCU_CLIE = @ISNU_DOCU
AND	T1.CO_EMPR = T4.CO_EMPR
AND	T4.CO_TIPO_DOCU = 'N/C'
And    T1.FE_AMAR <= @IDFE_CANC
AND    T1.TI_SITU != 'ANU'
AND    T2.CO_TIDO_ORIG = T4.CO_TIPO_DOCU 
AND    T2.NU_DOCU_ORIG = T4.NU_DOCU_CLIE 
AND    T1.CO_EMPR = T2.CO_EMPR  
AND    T1.NU_AMAR = T2.NU_AMAR
AND    T2.CO_EMPR = T4.CO_EMPR
AND    T1.CO_CLIE = T4.CO_CLIE
AND    T2.CO_TIDO_ORIG = T4.CO_TIPO_DOCU
AND    T2.NU_DOCU_ORIG = T4.NU_DOCU_CLIE
	  
AND	 T4.CO_ESTA_DOCU != 'ANU' 
And    T4.NU_REND_GAST IS NULL
AND 	 T4.CO_TIPO_DOCU = T5.TI_DOCU 

--4.- CANJES

-- SOLO MUESTRO LOS DOCUMENTOS QUE SE CANJEAN, NO LAS LETRAS GENERADAS
INSERT INTO #TWDOCU_CLIE_Q05_1 
(CO_EMPR, CO_TIPO_DOCU, NU_DOCU_CLIE, CO_CLIE, FE_REPO, 
CO_MONE, FA_TIPO_CAMB,
NU_CANJ,
IM_PAGO_NACI, IM_PAGO_EXTR, TI_DETA)
SELECT T1.CO_EMPR, T4.CO_TIPO_DOCU, T4.NU_DOCU_CLIE, T1.CO_CLIE, T1.FE_CANJ, 
T4.CO_MONE, T1.FA_TIPO_CAMB,
T1.NU_CANJ,
PatIndex( '0', LTRIM(RTRIM(STR(PatIndex(T4.CO_MONE, 'DOL')))))*( PatIndex(T5.ST_SIGN,'S') - PatIndex(T5.ST_SIGN,'N') )*
T2.IM_CANJ_MDOC,
PatIndex(T4.CO_MONE, 'DOL')*( PatIndex(T5.ST_SIGN,'S') - PatIndex(T5.ST_SIGN,'N') )*
T2.IM_CANJ_MDOC,
'4'
From  TCCANJ_CLIE T1 , TDCANJ_CLIE T2, TMDOCU_CLIE T4, TTDOCU_CNTB T5
Where  T4.CO_EMPR = @ISCO_EMPR
AND 	T4.CO_TIPO_DOCU = @ISTI_DOCU
AND 	T4.NU_DOCU_CLIE = @ISNU_DOCU
And	T1.CO_EMPR =   T4.CO_EMPR
And    T1.FE_CANJ  <= @IDFE_CANC
AND    T1.TI_SITU != 'ANU'
AND    T1.CO_EMPR = T2.CO_EMPR  
AND    T1.CO_CLIE = T2.CO_CLIE
AND    T1.NU_CANJ = T2.NU_CANJ
AND    T2.CO_CLIE = T4.CO_CLIE
AND    T2.CO_TIPO_DOCU = T4.CO_TIPO_DOCU 
AND    T2.NU_DOCU_CLIE = T4.NU_DOCU_CLIE 
AND	T4.CO_ESTA_DOCU != 'ANU' 
And    T4.NU_REND_GAST IS NULL
AND	 T4.CO_TIPO_DOCU = T5.TI_DOCU  

INSERT INTO #TWDOCU_CLIE_Q05_1 
(CO_EMPR, CO_TIPO_DOCU, NU_DOCU_CLIE, CO_CLIE, FE_REPO, 
CO_MONE, FA_TIPO_CAMB,
NU_CANJ,
IM_PAGO_NACI, IM_PAGO_EXTR, TI_DETA)
SELECT T1.CO_EMPR, 'LET', T4.NU_LETR_CLIE, T1.CO_CLIE, T1.FE_CANJ, 
T4.CO_MONE, T1.FA_TIPO_CAMB,
T1.NU_CANJ,
PatIndex( '0', LTRIM(RTRIM(STR(PatIndex(T4.CO_MONE, 'DOL')))))*( PatIndex(T5.ST_SIGN,'S') - PatIndex(T5.ST_SIGN,'N') )*
T2.IM_CANJ_MDOC,
PatIndex(T4.CO_MONE, 'DOL')*( PatIndex(T5.ST_SIGN,'S') - PatIndex(T5.ST_SIGN,'N') )*
T2.IM_CANJ_MDOC,
'4'
From  TCCANJ_CLIE T1 , TDCANJ_CLIE T2 /*(INDEX = IX_TDCANJ_CLIE_01)*/, 
      TMLETR_CLIE T4, TTDOCU_CNTB T5
Where  T4.CO_EMPR = @ISCO_EMPR
--AND    T4.CO_TIPO_DOCU = @ISTI_DOCU
AND    T4.NU_LETR_CLIE = @ISNU_DOCU
And    T1.CO_EMPR =   T4.CO_EMPR
And    T1.FE_CANJ  <= @IDFE_CANC
AND    T1.TI_SITU != 'ANU'
AND    T1.CO_EMPR = T2.CO_EMPR  
AND    T1.CO_CLIE = T2.CO_CLIE
AND    T1.NU_CANJ = T2.NU_CANJ
AND    T2.CO_EMPR = T4.CO_EMPR
AND    T2.CO_CLIE = T4.CO_CLIE
AND    T2.CO_TIPO_DOCU = 'LET'
AND    T2.CO_TIPO_DOCU = T5.TI_DOCU  
AND    T2.NU_DOCU_CLIE = T4.NU_LETR_CLIE 
AND    T4.CO_ESTA_DOCU != 'ANU' 


--5.- RENOVACIONES

-- SOLO MUESTRO LOS DOCUMENTOS QUE SE RENUEVAN, NO LAS LETRAS GENERADAS
INSERT INTO #TWDOCU_CLIE_Q05_1 
(CO_EMPR, CO_TIPO_DOCU, NU_DOCU_CLIE, CO_CLIE, FE_REPO, 
CO_MONE, FA_TIPO_CAMB,
NU_RENO,
IM_PAGO_NACI, IM_PAGO_EXTR, TI_DETA)
SELECT T1.CO_EMPR, T4.CO_TIPO_DOCU, T4.NU_DOCU_CLIE, T1.CO_CLIE, T1.FE_RENO,
T4.CO_MONE, T1.FA_TIPO_CAMB,
T1.NU_RENO,
PatIndex( '0', LTRIM(RTRIM(STR(PatIndex(T4.CO_MONE, 'DOL')))))*( PatIndex(T5.ST_SIGN,'S') - PatIndex(T5.ST_SIGN,'N') )*
(T2.IM_RENO_MDOC),
PatIndex(T4.CO_MONE, 'DOL')*( PatIndex(T5.ST_SIGN,'S') - PatIndex(T5.ST_SIGN,'N') )*
(T2.IM_RENO_MDOC),
'5'
From   TCRENO_CLIE T1 , TDRENO_CLIE T2, TMDOCU_CLIE T4, TTDOCU_CNTB T5
Where  T4.CO_EMPR = @ISCO_EMPR
AND 	T4.CO_TIPO_DOCU = @ISTI_DOCU
AND 	T4.NU_DOCU_CLIE = @ISNU_DOCU

And    T1.FE_RENO  <= @IDFE_CANC
AND    T1.TI_SITU != 'ANU'
AND    T1.CO_EMPR = T2.CO_EMPR  
AND    T1.CO_CLIE = T2.CO_CLIE
AND    T1.NU_RENO = T2.NU_RENO
AND    T2.CO_CLIE = T4.CO_CLIE
AND    T2.CO_TIPO_DOCU = T4.CO_TIPO_DOCU 
AND    T2.NU_DOCU_CLIE = T4.NU_DOCU_CLIE 
AND	 T4.CO_EMPR =   T1.CO_EMPR
AND	 T4.CO_ESTA_DOCU != 'ANU' 
And    T4.NU_REND_GAST IS NULL
AND	 T4.CO_TIPO_DOCU = T5.TI_DOCU

INSERT INTO #TWDOCU_CLIE_Q05_1 
(CO_EMPR, CO_TIPO_DOCU, NU_DOCU_CLIE, CO_CLIE, FE_REPO, 
CO_MONE, FA_TIPO_CAMB,
NU_RENO,
IM_PAGO_NACI, IM_PAGO_EXTR, TI_DETA)
SELECT T1.CO_EMPR, 'LET', T4.NU_LETR_CLIE, T1.CO_CLIE, T1.FE_RENO,
T4.CO_MONE, T1.FA_TIPO_CAMB,
T1.NU_RENO,
PatIndex( '0', LTRIM(RTRIM(STR(PatIndex(T4.CO_MONE, 'DOL')))))*(T2.IM_RENO_MDOC),
PatIndex(T4.CO_MONE, 'DOL')*(T2.IM_RENO_MDOC), 
'5'
From   TCRENO_CLIE T1 , TDRENO_CLIE T2, TMLETR_CLIE T4
Where  	T4.CO_EMPR = @ISCO_EMPR
AND    T4.NU_LETR_CLIE = @ISNU_DOCU
And   	T1.FE_RENO  <= @IDFE_CANC
AND    	T1.TI_SITU != 'ANU'
AND    	T1.CO_EMPR = T2.CO_EMPR  
AND    	T1.CO_CLIE = T2.CO_CLIE
AND    	T1.NU_RENO = T2.NU_RENO
AND    	T2.CO_CLIE = T4.CO_CLIE
AND    	T2.CO_TIPO_DOCU = 'LET'
AND    	T2.NU_DOCU_CLIE = T4.NU_LETR_CLIE 
AND	T4.CO_EMPR =   T1.CO_EMPR
AND	T4.CO_ESTA_DOCU != 'ANU'
AND 	T4.NU_RENO = T1.NU_RENO



--6.- APLICACION DE DOCUMENTOS A ANTICIPOS

-- SOLO MUESTRO LOS DOCUMENTOS QUE SE APLICAN
INSERT INTO #TWDOCU_CLIE_Q05_1 
(CO_EMPR, CO_TIPO_DOCU, NU_DOCU_CLIE, CO_CLIE, FE_REPO, 
CO_MONE, FA_TIPO_CAMB,
NU_AMAR,
IM_PAGO_NACI, IM_PAGO_EXTR, TI_DETA)
SELECT T1.CO_EMPR, T4.CO_TIPO_DOCU, T4.NU_DOCU_CLIE, T4.CO_CLIE, T1.FE_AMAR,
T4.CO_MONE, T1.FA_TIPO_CAMB,
T1.NU_AMAR,
PatIndex(T2.ST_CIER_DOCU, 'N')*
PatIndex( '0', LTRIM(RTRIM(STR(PatIndex(T4.CO_MONE, 'DOL')))))*
( PatIndex(T5.ST_SIGN,'S') - PatIndex(T5.ST_SIGN,'N') )*
(T2.IM_CONV_DOCU) +
PatIndex(T2.ST_CIER_DOCU, 'S')*
PatIndex( '0', LTRIM(RTRIM(STR(PatIndex(T4.CO_MONE, 'DOL')))))*
( PatIndex(T5.ST_SIGN,'S') - PatIndex(T5.ST_SIGN,'N') )*
(T2.IM_SALD_DOCU),
PatIndex(T2.ST_CIER_DOCU, 'N')*
PatIndex(T4.CO_MONE, 'DOL')*
( PatIndex(T5.ST_SIGN,'S') - PatIndex(T5.ST_SIGN,'N') )*
(T2.IM_CONV_DOCU)+
PatIndex(T2.ST_CIER_DOCU, 'S')*
PatIndex(T4.CO_MONE, 'DOL')*
( PatIndex(T5.ST_SIGN,'S') - PatIndex(T5.ST_SIGN,'N') )*
(T2.IM_SALD_DOCU),
'6'
From   TCAMAR_ANTC T1 , TDAMAR_ANTC T2, TMDOCU_CLIE T4, TTDOCU_CNTB T5
Where  T4.CO_EMPR = @ISCO_EMPR
AND 	T4.CO_TIPO_DOCU = @ISTI_DOCU
AND 	T4.NU_DOCU_CLIE = @ISNU_DOCU
And    T1.FE_AMAR  <= @IDFE_CANC
AND    T1.TI_SITU != 'ANU'
AND    T1.CO_EMPR = T2.CO_EMPR  
AND    T1.NU_AMAR = T2.NU_AMAR
AND	 T2.CO_EMPR =   T4.CO_EMPR
AND    T2.TI_ENTI = @ISTI_AUXI_CLIE
AND    T2.TI_DOCU_ENTI = T4.CO_TIPO_DOCU 
AND    T2.NU_DOCU_ENTI = T4.NU_DOCU_CLIE 
AND	 T4.CO_ESTA_DOCU != 'ANU' 
And    T4.NU_REND_GAST IS NULL
AND	 T4.CO_TIPO_DOCU = T5.TI_DOCU

--If PatIndex('%LET%', @ISTI_DOCU) > 0 
INSERT INTO #TWDOCU_CLIE_Q05_1 
(CO_EMPR, CO_TIPO_DOCU, NU_DOCU_CLIE, CO_CLIE, FE_REPO, 
CO_MONE, FA_TIPO_CAMB,
NU_AMAR,
IM_PAGO_NACI, IM_PAGO_EXTR, TI_DETA)
SELECT T1.CO_EMPR, 'LET', T4.NU_LETR_CLIE, T4.CO_CLIE, T1.FE_AMAR,
T4.CO_MONE, T1.FA_TIPO_CAMB,
T1.NU_AMAR,
PatIndex(T2.ST_CIER_DOCU, 'N')*
PatIndex( '0', LTRIM(RTRIM(STR(PatIndex(T4.CO_MONE, 'DOL')))))*
(T2.IM_CONV_DOCU)+
PatIndex(T2.ST_CIER_DOCU, 'S')*
PatIndex( '0', LTRIM(RTRIM(STR(PatIndex(T4.CO_MONE, 'DOL')))))*
(T2.IM_SALD_DOCU),
PatIndex(T2.ST_CIER_DOCU, 'N')*
PatIndex(T4.CO_MONE, 'DOL')*
(T2.IM_CONV_DOCU)+
PatIndex(T2.ST_CIER_DOCU, 'S')*
PatIndex(T4.CO_MONE, 'DOL')*
(T2.IM_SALD_DOCU), 
'6'
From   TCAMAR_ANTC T1 , TDAMAR_ANTC T2, TMLETR_CLIE T4, #TWDOCU_CLIE_Q05_1 T5
Where  T4.CO_EMPR = @ISCO_EMPR
AND	 T4.CO_ESTA_DOCU != 'ANU'
And    T1.FE_AMAR <= @IDFE_CANC
AND    T1.TI_SITU != 'ANU'
AND	T2.TI_ENTI = @ISTI_AUXI_CLIE
AND    T2.TI_DOCU_ENTI = 'LET'
AND    T1.CO_EMPR = T2.CO_EMPR  
AND    T1.NU_AMAR = T2.NU_AMAR
AND	 T2.CO_EMPR =   T4.CO_EMPR
AND    T2.CO_ENTI = T4.CO_CLIE
AND    T2.NU_DOCU_ENTI = T4.NU_LETR_CLIE

AND	 T2.CO_EMPR = T5.CO_EMPR
AND	 T2.TI_DOCU_ENTI = T5.CO_TIPO_DOCU
AND 	 T2.NU_DOCU_ENTI = T5.NU_DOCU_CLIE


--7.- ANTICIPOS - PAGOS BANCOS 

-- ANTICIPOS SIN APLICACION

INSERT INTO #TWDOCU_CLIE_Q05_1 
(CO_EMPR, CO_TIPO_DOCU, NU_DOCU_CLIE, CO_CLIE, FE_REPO, 
CO_MONE, FA_TIPO_CAMB,
CO_BANC, NU_CNTA_BANC, NU_COMP_CABA,
IM_PAGO_NACI, IM_PAGO_EXTR, TI_DETA)
Select T2.CO_EMPR, T2.CO_TIPO_DOCU, T2.NU_DOCU_BANC, T2.CO_ENTI, T2.FE_OPER,
T3.CO_MONE, T2.FA_TIPO_CAMB,
T2.CO_BANC, T2.NU_CNTA_BANC, T2.NU_COMP_BANC,

PatIndex('0', LTRIM(RTRIM(STR(PatIndex(T3.CO_MONE, 'DOL')))))*T2.IM_MOVI,
PatIndex(T3.CO_MONE, 'DOL')*T2.IM_MOVI, 

'7'
From  TCMOVI_BANC T2, TMCNTA_BANC T3,  TTTIPO_OPER T5
Where  T2.CO_EMPR = @ISCO_EMPR
AND 	T2.CO_TIPO_DOCU = @ISTI_DOCU

And    T2.TI_MOVI_BANC = 'I' 
And    T2.TI_ENTI = @ISTI_AUXI_CLIE
And    T2.FE_OPER  <= @IDFE_CANC
And    T2.TI_SITU != 'ANU' 

And    T2.CO_EMPR = T3.CO_EMPR  
And    T2.CO_BANC = T3.CO_BANC  
And    T2.NU_CNTA_BANC  = T3.NU_CNTA_BANC

--AND	 T2.CO_EMPR = T4.CO_EMPR
--AND 	 T2.CO_ENTI = T4.CO_CLIE 

AND	 T2.CO_EMPR = T5.CO_EMPR
AND 	 T2.CO_TIPO_OPER = T5.CO_TIPO_OPER
AND    T5.ST_AUXI_0002 = 'S'
AND	 NOT EXISTS ( SELECT T6.CO_EMPR
			FROM TCAMAR_ANTC T6
			WHERE T6.TI_SITU != 'ANU'
			AND T6.FE_AMAR < @IDFE_CANC
			AND	 T2.CO_EMPR = T6.CO_EMPR
			And    T2.CO_BANC = T6.CO_BANC  
			And    T2.NU_CNTA_BANC  = T6.NU_CNTA_BANC
			And    T2.AA_BNCO = T6.AA_BNCO
			And    T2.MM_BNCO = T6.MM_BNCO
			And    T2.NU_COMP_BANC = T6.NU_COMP_BANC )  


--8.- ANTICIPOS - PAGOS BANCOS 
-- ANTICIPO CON APLICACION 
INSERT INTO #TWDOCU_CLIE_Q05_1 
(CO_EMPR, CO_TIPO_DOCU, NU_DOCU_CLIE, CO_CLIE, FE_REPO, 
CO_MONE, FA_TIPO_CAMB,
CO_BANC, NU_CNTA_BANC, NU_COMP_CABA,
IM_PAGO_NACI, IM_PAGO_EXTR, TI_DETA)
Select T2.CO_EMPR, T2.CO_TIPO_DOCU, T2.NU_DOCU_BANC, T2.CO_ENTI, MAX(T2.FE_OPER),
T3.CO_MONE, MAX(T2.FA_TIPO_CAMB),
T2.CO_BANC, T2.NU_CNTA_BANC, T2.NU_COMP_BANC,
MAX(PatIndex('0', LTRIM(RTRIM(STR(PatIndex(T3.CO_MONE, 'DOL')))))*T2.IM_MOVI) -
ISNULL( SUM( PatIndex('0', LTRIM(RTRIM(STR(PatIndex(T3.CO_MONE, 'DOL')))))*T7.IM_DETA_NEGA ), 0),
MAX(PatIndex(T3.CO_MONE, 'DOL')*T2.IM_MOVI) - 
ISNULL( SUM( PatIndex(T3.CO_MONE, 'DOL')*T7.IM_DETA_NEGA ), 0), 
'8'
From  TCMOVI_BANC T2 
Inner Join TMCNTA_BANC T3
On   T2.CO_EMPR = T3.CO_EMPR  
And  T2.CO_BANC = T3.CO_BANC  
And  T2.NU_CNTA_BANC  = T3.NU_CNTA_BANC
Inner join TTTIPO_OPER T5
On   T2.CO_EMPR = T5.CO_EMPR
AND  T2.CO_TIPO_OPER = T5.CO_TIPO_OPER
AND  T5.ST_AUXI_0002 = 'S'
Inner Join TCAMAR_ANTC T6 
On   T2.CO_EMPR = T6.CO_EMPR
And  T2.CO_BANC = T6.CO_BANC  
And  T2.NU_CNTA_BANC  = T6.NU_CNTA_BANC
And  T2.AA_BNCO = T6.AA_BNCO
And  T2.MM_BNCO = T6.MM_BNCO
And  T2.NU_COMP_BANC = T6.NU_COMP_BANC
Left Outer Join TDAMAR_ANTC T7
On  T6.CO_EMPR = T7.CO_EMPR 
AND T6.NU_AMAR = T7.NU_AMAR
AND T6.TI_SITU != 'ANU'
And T6.FE_AMAR  <= @IDFE_CANC
Where T2.CO_EMPR = @ISCO_EMPR
AND    T2.CO_TIPO_DOCU = @ISTI_DOCU
And    T2.TI_MOVI_BANC = 'I' 
And    T2.TI_ENTI = 'C'
And    T2.TI_SITU != 'ANU' 





GROUP BY T2.CO_EMPR, T2.CO_TIPO_DOCU, T2.NU_DOCU_BANC, T2.CO_ENTI, T3.CO_MONE, 
	   T2.CO_BANC, T2.NU_CNTA_BANC, T2.NU_COMP_BANC  


	-- DATOS DE LETRAS DE CANJES Y RENOVACIONES
	SELECT @VSCO_EMPR = ''
	SELECT @VSCO_CLIE = ''
	SELECT @VSNU_CANJ = ''
	SELECT @VSDE_LETR = ''

	DECLARE CU_TWDOCU_CLIE_Q05_1 CURSOR FOR
	Select DISTINCT T1.CO_EMPR, T1.CO_CLIE, T1.NU_CANJ, T2.NU_LETR_CLIE
	From  #TWDOCU_CLIE_Q05_1 T1, TDCANJ_CLIE T2
	WHERE T1.CO_EMPR = T2.CO_EMPR
	AND T1.CO_CLIE = T2.CO_CLIE
	AND T1.NU_CANJ = T2.NU_CANJ
	AND T2.NU_LETR_CLIE IS NOT NULL
	ORDER BY 1, 2, 3  


	Open CU_TWDOCU_CLIE_Q05_1
	Fetch  CU_TWDOCU_CLIE_Q05_1 INTO  @CSCO_EMPR, @CSCO_CLIE, @CSNU_CANJ, @CSNU_LETR_CLIE
	WHILE @@FETCH_STATUS = 0
	Begin

		IF @CSCO_EMPR + @CSCO_CLIE+ @CSNU_CANJ = @VSCO_EMPR + @VSCO_CLIE + @VSNU_CANJ 
			SELECT @VSDE_LETR = RTRIM(@VSDE_LETR) + ', ' + RTRIM(@CSNU_LETR_CLIE)
		
		IF @CSCO_EMPR+@CSCO_CLIE+@CSNU_CANJ != @VSCO_EMPR+@VSCO_CLIE+@VSNU_CANJ 	
		BEGIN
			IF ISNULL(LEN(@VSDE_LETR),0) > 0 
				UPDATE #TWDOCU_CLIE_Q05_1
				SET NU_CNTA_BANC = @VSDE_LETR
				WHERE CO_EMPR = @VSCO_EMPR
				AND CO_CLIE = @VSCO_CLIE
				AND NU_CANJ = @VSNU_CANJ
							
			SELECT @VSDE_LETR = @CSNU_LETR_CLIE
			SELECT @VSCO_EMPR = @CSCO_EMPR
			SELECT @VSCO_CLIE = @CSCO_CLIE
      			SELECT @VSNU_CANJ = @CSNU_CANJ
		END	
		Fetch  CU_TWDOCU_CLIE_Q05_1 INTO  @CSCO_EMPR, @CSCO_CLIE, @CSNU_CANJ, @CSNU_LETR_CLIE
	End
	Close CU_TWDOCU_CLIE_Q05_1
	DEALLOCATE CU_TWDOCU_CLIE_Q05_1

	UPDATE #TWDOCU_CLIE_Q05_1
	SET NU_CNTA_BANC = @VSDE_LETR
	WHERE CO_EMPR = @VSCO_EMPR
	AND CO_CLIE = @VSCO_CLIE
	AND NU_CANJ = @VSNU_CANJ
		
	SELECT @VSCO_EMPR = ''
	SELECT @VSCO_CLIE = ''
	SELECT @VSNU_RENO = ''
	SELECT @VSDE_LETR = ''


	DECLARE CU_TWDOCU_CLIE_Q05_2 CURSOR FOR
	Select DISTINCT T1.CO_EMPR, T1.CO_CLIE, T1.NU_RENO, T2.NU_LETR_CLIE
	From  #TWDOCU_CLIE_Q05_1 T1, TDRENO_CLIE T2
	WHERE T1.CO_EMPR = T2.CO_EMPR
	AND T1.CO_CLIE = T2.CO_CLIE
	AND T1.NU_RENO = T2.NU_RENO
	AND T2.NU_LETR_CLIE IS NOT NULL
	ORDER BY 1, 2, 3

	Open CU_TWDOCU_CLIE_Q05_2
	Fetch  CU_TWDOCU_CLIE_Q05_2 INTO  @CSCO_EMPR, @CSCO_CLIE, @CSNU_RENO, @CSNU_LETR_CLIE
	WHILE @@FETCH_STATUS = 0
	Begin

		IF @CSCO_EMPR+@CSCO_CLIE+@CSNU_RENO = @VSCO_EMPR+@VSCO_CLIE+@VSNU_RENO 
			SELECT @VSDE_LETR = RTRIM(@VSDE_LETR)+', '+RTRIM(@CSNU_LETR_CLIE)
		

		IF @CSCO_EMPR+@CSCO_CLIE+@CSNU_RENO != @VSCO_EMPR+@VSCO_CLIE+@VSNU_RENO 
		BEGIN
			IF ISNULL(LEN(@VSDE_LETR),0) > 0 
				UPDATE #TWDOCU_CLIE_Q05_1
				SET NU_CNTA_BANC = @VSDE_LETR
				WHERE CO_EMPR = @VSCO_EMPR
				AND CO_CLIE = @VSCO_CLIE
				AND NU_RENO = @VSNU_RENO
							
			SELECT @VSDE_LETR = @CSNU_LETR_CLIE
			SELECT @VSCO_EMPR = @CSCO_EMPR
      			SELECT @VSCO_CLIE = @CSCO_CLIE
      			SELECT @VSNU_RENO = @CSNU_RENO
		END	
		Fetch  CU_TWDOCU_CLIE_Q05_2 INTO  @CSCO_EMPR, @CSCO_CLIE, @CSNU_RENO, @CSNU_LETR_CLIE
	End
	Close CU_TWDOCU_CLIE_Q05_2
	DEALLOCATE CU_TWDOCU_CLIE_Q05_2

	UPDATE #TWDOCU_CLIE_Q05_1
	SET NU_CNTA_BANC = @VSDE_LETR
	WHERE CO_EMPR = @VSCO_EMPR
	AND CO_CLIE = @VSCO_CLIE
	AND NU_RENO = @VSNU_RENO
	
-------------------------
    	SELECT	CO_EMPR, CO_TIPO_DOCU, NU_DOCU_CLIE, FE_REPO, 
		ISNULL( CO_BANC, ' '), ISNULL( NU_CNTA_BANC, ' '), 
		ISNULL( CO_CAJA, ' '), ISNULL( NU_COMP_CABA, ' '), 
		ISNULL( NU_AMAR, ' '), ISNULL( NU_CANJ, ' '), ISNULL( NU_RENO, ' '), 
 		ISNULL( IM_CXCB_NACI, 0 ),  ISNULL( IM_CXCB_EXTR, 0 ),
		ISNULL( IM_PAGO_NACI, 0 ),  ISNULL( IM_PAGO_EXTR, 0 ),
		ISNULL( FA_TIPO_CAMB, 0 ),  TI_DETA
	FROM #TWDOCU_CLIE_Q05_1
	ORDER BY FE_REPO, CO_TIPO_DOCU, NU_DOCU_CLIE


RETURN
/*------------------------- FIN ------------------------------ */


GO
